package gem.model.dao.mysql;

import gem.model.core.Course;
import java.sql.SQLException;

import gem.database.Database;
import gem.model.dao.Common;
import gem.model.dao.CourseDAO;

public class MySqlCourseDAO implements CourseDAO
{
    private static Course defaultCourse = null;

    public static Course testGetDefaultCourse() throws SQLException
    {
        return getDefaultCourse();
    }

    protected static final Course getDefaultCourse() throws SQLException
    {
        if( defaultCourse == null)
        {
            String query = "SELECT * FROM Course WHERE coursetitle = " + '"' + '"' + ";";
            java.sql.Connection conn = null;
            java.sql.ResultSet rs = null;
            try
            {
                conn = Database.getConnection();
                rs = conn.createStatement().executeQuery(query);

                if( rs.first() )
                {
                    defaultCourse = new Course().setId( rs.getInt(Common.CourseId) ).setTitle(rs.getString( Common.CourseTitle));
                }
                else // there is no entry, we are creating new one!
                {
                    query = "INSERT INTO course( coursetitle ) VALUES( '" + "" + "'); ";
                    int newId;

                    java.sql.PreparedStatement pStat = conn.prepareStatement(query, java.sql.Statement.RETURN_GENERATED_KEYS);
                    int numRows = pStat.executeUpdate();

                    if (numRows == 0)
                    {
                        throw new SQLException("Cannot insert default course " );
                    }

                    rs = pStat.getGeneratedKeys();

                    if (rs.first())
                    {
                        newId = rs.getInt(1);
                        defaultCourse = new Course().setId(newId).setTitle("");
                    }
                    else
                    {
                        throw new SQLException("Cannot insert default course " );
                    }
                }
            }
            finally
            {
                if( rs != null )
                {
                    rs.close();
                }
                if( conn != null )
                {
                    conn.close();
                }
            }
        }
        
        return defaultCourse;
    }

    @Override
    public int insertCourse(Course c) throws SQLException
    {
        // TODO Auto-generated method stub
        // Inserting new course in Course table as well as in ScienceDomainCourse table
        int sdId = c.getScienceDomain().getId();
        int depId = c.getScienceDomain().getDepartment().getId();
        String query = "INSERT INTO course( courseid, coursetitle ) "
                + "VALUES( '" + c.getId() + "', '" + c.getTitle() + "'); ";

        int newId = 0;

        java.sql.Connection conn = null;
        java.sql.ResultSet rs = null;

        try
        {
            conn = Database.getConnection();
            java.sql.PreparedStatement pStat = conn.prepareStatement(query, java.sql.Statement.RETURN_GENERATED_KEYS);
            int numRows = pStat.executeUpdate();

            if (numRows == 0)
            {
                throw new SQLException("Cannot insert course " + c);
            }

            rs = pStat.getGeneratedKeys();

            if (rs.first())
            {
                newId = rs.getInt(1);
                c.setId(newId);
            }
            else
            {
                throw new SQLException("Cannot insert course " + c);
            }


            query = "INSERT INTO sciencedomaincourses( departmentid, sciencedomainid, courseid ) VALUES "
                    + "( " + depId + " , " + sdId + " , " + newId + " ); ";

            conn.createStatement().executeUpdate(query);
        } finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        }

        return newId;
    }

    @Override
    public boolean deleteCourse(Course c) throws java.sql.SQLException
    {
        // TODO:
        // - remove from ScienceDomainCourse
        // - remove from Course
        // - remove from EmployeeCourse
        int cId = c.getId();
        String query = "DELETE FROM course where courseid = '" + cId + "' ;";

        java.sql.Connection conn = null;
        try
        {
            conn = Database.getConnection();

            conn.createStatement().executeUpdate(query);
            //conn.createStatement().executeUpdate("DELETE FROM employeecourse WHERE courseid = '" + cId + "' ;" );
            //conn.createStatement().executeUpdate("DELETE FROM sciencedomaincourse WHERE courseid = '" + cId + "' ;" );
            // zbog stranih kljuceva cemo obrisati i sve iz
            // 	sciencedomaincourse i employeeCourse
        } finally
        {
            if (conn != null)
            {
                conn.close();
            }
        }

        return true;
    }

    @Override
    public boolean updateCourse(Course c) throws SQLException
    {
        // TODO:
        // - update in Course table
        String query = "UPDATE course SET coursetitle = '" + c.getTitle() + "' WHERE courseid = '"
                + c.getId() + "' ;";
        java.sql.Connection conn = null;
        try
        {
            conn = Database.getConnection();
            conn.createStatement().executeUpdate(query);
        } finally
        {
            if (conn != null)
            {
                conn.close();
            }
        }

        return true;
    }

    @Override
    public Course findCourse(String s) throws SQLException
    {
        String query = "SELECT * FROM course WHERE coursetitle = '" + s + "' ; ";
        java.sql.Connection conn = null;
        java.sql.ResultSet rs = null;
        Course c = null;

        try
        {
            conn = Database.getConnection();
            rs = conn.createStatement().executeQuery(query);
            if (rs.first())
            {
                c = new Course().setId(rs.getInt(Common.CourseId)).setTitle(rs.getString(Common.CourseTitle));
            }
        } finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        }

        return c;
    }
}
